from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:123456@localhost/t'

# app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@localhost/test'  # 使用 SQLite 数据库
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):
        return f'<User {self.username}>'

# 创建数据库表
with app.app_context():
    db.create_all()

@app.route('/add_user')
def add_user():
    new_user = User(username='john_doe', email='john@example.com')
    db.session.add(new_user)
    db.session.commit()
    return 'User added!'

@app.route('/get_users')
def get_users():
    users = User.query.all()  # 获取所有用户
    return '<br>'.join([f'{user.username} ({user.email})' for user in users])

@app.route('/update_user/<int:user_id>')
def update_user(user_id):
    user = User.query.get(user_id)
    if user:
        user.username = 'new_username'
        db.session.commit()
        return 'User updated!'
    return 'User not found!'

@app.route('/delete_user/<int:user_id>')
def delete_user(user_id):
    user = User.query.get(user_id)
    if user:
        db.session.delete(user)
        db.session.commit()
        return 'User deleted!'
    return 'User not found!'


@app.route('/get_userByname')
def get_userByname():
    users = User.query.filter_by(username='john_doe').all()
    return '<br>'.join([f'{user.username} ({user.email})' for user in users])


@app.route('/get_userBynameCondtion')
def get_userBynameCondtion():
    from sqlalchemy import or_
    users = User.query.filter(or_(User.username == 'john_doe', User.email == 'john@example.com')).all()
    return '<br>'.join([f'{user.username} ({user.email})' for user in users])


@app.route('/get_userBypage')
def get_userBypage():
    from sqlalchemy import or_
    users = User.query.order_by(User.username).paginate(page=1, per_page=10)
    return '<br>'.join([f'{user.username} ({user.email})' for user in users])


@app.route('/raw_sql')
def raw_sql():
    result = db.session.execute('SELECT * FROM user')
    return '<br>'.join([str(row) for row in result])





if __name__ == '__main__':
    app.run(debug=True)